﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.IO;
using System.Web;
using FrameworkManapro.Utilities;
using FrameworkManapro.BusinessModel;
using GMAC.ReportingSystem.Business.Entities;
using GMAC.ReportingSystem.Business.Entities.Configuration;

namespace GMAC.ReportingSystem.DataAccess
{
    public class LaoSalvageAccountsAccess
    {

        public void GenerateUploadFile(int UserId)
        {
            ArrayList parameters = new ArrayList { UserId };
            DataAccessComponent.ExecuteOperation("procLaoSalvageAccountsMerge", parameters);
        } // GenerateUploadFile


        public StringLaoSalvageAccountCollection GetLaoSalvageAccounts(LoadFileStatus status, DateTime processDate, int user)
        {
            StringLaoSalvageAccountCollection Records = new StringLaoSalvageAccountCollection();
            StringLaoSalvageAccount Record;
            ArrayList parameters = new ArrayList { status, processDate, user };
            DataTable dt = DataAccessComponent.ExecuteSelect("procTemporaryLaoSalvageAccountsByFilter", parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    Record = new StringLaoSalvageAccount
                    {
                        LoadDate                = (DateTime)row["LoadDate"],
                        AccountNumber           = row["AccountNumber"].ToString(),
                        AccountStatusCode       = row["AccountStatusCode"].ToString(),
                        AccountStatusDate       = row["AccountStatusDate"].ToString(),
                        DealerName              = row["DealerName"].ToString(),
                        DealerNumber            = row["DealerNumber"].ToString(),
                        ThirdPartyName          = row["ThirdPartyName"].ToString(),
                        ThirdPartyTypeCode      = row["ThirdPartyTypeCode"].ToString(),
                        CurrentTotalAmount      = row["CurrentTotalAmount"].ToString(),
                        RejectionReason         = row["RejectionReason"].ToString()
                    }; // Record = new StringLaoSalvageAccount

                   Records.Add(Record);
                } // foreach
            } // if
            return Records;
        } // GetLaoSalvageAccounts

        /// <summary>
        /// Inserta en una tabla temporal los registros del archivo excel
        /// </summary>
        /// <param name="processDate">Fecha en la que se esta procesando el archivo</param>
        /// <param name="UserId">Codigo del usuario que esta procesando el archivo</param>
        /// <param name="Records">Coleccion de registros a insertar en la tabla temporal</param>
        public void UploadLoanFile(DateTime processDate, int UserId, StringLaoSalvageAccountCollection Records, ref StringLaoSalvageAccountCollection LAOInvalids, ref StringLaoSalvageAccountCollection LAOValids)
        {
            try
            {

                DataRow row;
                DataTable dt = new DataTable();

                dt.Columns.Add("LoadDate", typeof(DateTime));
                dt.Columns.Add("AccountNumber", typeof(string));
                dt.Columns.Add("AccountStatusCode", typeof(string));
                dt.Columns.Add("AccountStatusDate", typeof(string));
                dt.Columns.Add("DealerName", typeof(string));
                dt.Columns.Add("DealerNumber", typeof(string));
                dt.Columns.Add("ThirdPartyName", typeof(string));
                dt.Columns.Add("ThirdPartyTypeCode", typeof(string));
                dt.Columns.Add("CurrentTotalAmount", typeof(string));
                dt.Columns.Add("UserID", typeof(string));
                //dt.Columns.Add("bitStatus", typeof(bool));

                foreach (StringLaoSalvageAccount Record in Records)
                {
                    row = dt.NewRow();
                    row["LoadDate"]             = processDate;
                    row["AccountNumber"]        = Record.AccountNumber;
                    row["AccountStatusCode"]    = Record.AccountStatusCode;
                    row["AccountStatusDate"]    = Record.AccountStatusDate;
                    row["DealerName"]           = Record.DealerName;
                    row["DealerNumber"]         = Record.DealerNumber;
                    row["ThirdPartyName"]       = Record.ThirdPartyName;
                    row["ThirdPartyTypeCode"]   = Record.ThirdPartyTypeCode;
                    row["CurrentTotalAmount"]   = Record.CurrentTotalAmount;
                    row["UserID"]               = UserId;
                    // Inserta el registro como Correcto 
                    // y despues se ejecuta la validacion en el SP que puede cambiar el estatus a invalido
                    //row["bitStatus"]            = OK;

                    dt.Rows.Add(row);
                } // foreach

                // asigna los parametros para llamar el SP
                var parameters = new ArrayList { processDate, UserId, dt };

                // Llama el SP que inserta en la tabla temporal
                DataAccessComponent.ExecuteOperation("procTemporaryLaoSalvageAccountsInsert", parameters);

                // Llama los metodos que obtiene la lista de registros validos e invalidos para retornarlos en la funcion
                LAOInvalids = GetLaoSalvageAccounts(LoadFileStatus.Invalid, processDate, UserId);
                LAOValids = GetLaoSalvageAccounts(LoadFileStatus.Valid, processDate, UserId);
            } // try
            catch (Exception)
            {
                throw;
            } // catch


        } // UploadLoanFile


                /// <summary>
        /// Recibe una coleccion y la retorna ordenada segun indiquen los parametros
        /// </summary>
        /// <param name="records">Coleccion de valores a ordenar</param>
        /// <param name="column">Campo por el que va a ordenar</param>
        /// <param name="order">"ASC" o "DESC"</param>
        /// <returns>Lista ordenada</returns>
        public StringLaoSalvageAccountCollection sord(StringLaoSalvageAccountCollection records, string column, string order)
        {
            StringLaoSalvageAccountCollection Result = new StringLaoSalvageAccountCollection();
            StringLaoSalvageAccount NewRecord;
            DataRow row;
            DataTable dt = new DataTable();

            dt.Columns.Add("LoadDate", typeof(DateTime));
            dt.Columns.Add("AccountNumber", typeof(string));
            dt.Columns.Add("AccountStatusCode", typeof(string));
            dt.Columns.Add("AccountStatusDate", typeof(string));
            dt.Columns.Add("DealerName", typeof(string));
            dt.Columns.Add("DealerNumber", typeof(string));
            dt.Columns.Add("ThirdPartyName", typeof(string));
            dt.Columns.Add("ThirdPartyTypeCode", typeof(string));
            dt.Columns.Add("CurrentTotalAmount", typeof(string));
            dt.Columns.Add("UserID", typeof(string));
            dt.Columns.Add("RejectionReason", typeof(string));
            dt.Columns.Add("RejectionReason_Small", typeof(string));

            // Captura todos los registros de la coleccion en la tabla para ordenarlos
            foreach (StringLaoSalvageAccount Record in records)
            {
                row = dt.NewRow();

                row["LoadDate"]             = Record.LoadDate;
                row["AccountNumber"]        = Record.AccountNumber;
                row["AccountStatusCode"]    = Record.AccountStatusCode;
                row["AccountStatusDate"]    = Record.AccountStatusDate;
                row["DealerName"]           = Record.DealerName;
                row["DealerNumber"]         = Record.DealerNumber;
                row["ThirdPartyName"]       = Record.ThirdPartyName;
                row["ThirdPartyTypeCode"]   = Record.ThirdPartyTypeCode;
                row["CurrentTotalAmount"]   = Record.CurrentTotalAmount;
                row["RejectionReason"]      = Record.RejectionReason;
                row["RejectionReason_Small"] = Record.RejectionReason_Small;

                dt.Rows.Add(row);
            } // foreach


            // Ordena los valores
            dt.DefaultView.Sort = column + " " + order;

            dt = dt.DefaultView.ToTable();

            // vuelve a asignar los registros en la coleccion con el nuevo orden
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow newrow in dt.Rows)
                {
                    NewRecord = new StringLaoSalvageAccount
                    {
                        LoadDate                = (DateTime)newrow["LoadDate"],
                        AccountNumber           = newrow["AccountNumber"].ToString(),
                        AccountStatusCode       = newrow["AccountStatusCode"].ToString(),
                        AccountStatusDate       = newrow["AccountStatusDate"].ToString(),
                        DealerName              = newrow["DealerName"].ToString(),
                        DealerNumber            = newrow["DealerNumber"].ToString(),
                        ThirdPartyName          = newrow["ThirdPartyName"].ToString(),
                        ThirdPartyTypeCode      = newrow["ThirdPartyTypeCode"].ToString(),
                        CurrentTotalAmount      = newrow["CurrentTotalAmount"].ToString(),
                        RejectionReason         = newrow["RejectionReason"].ToString()


                    }; // Record = new

                    Result.Add(NewRecord);
                } // foreach
            } // if

            // Retorna los valores en la coleccion ordenados
            return Result;

        } // sord

    } // class
} // namespace
